package lxt.dao.impl;

import java.util.List;
import java.util.Map;

import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;

import lxt.dao.QueryDataDao;

public class QueryDataDaoImpl extends ABseDaoImpl implements QueryDataDao {

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryOweForPage(String clientName,
			String orderId, String startDate, String endDate, Integer offset,
			Integer pageCounts) {
		StringBuffer sql = new StringBuffer();
		sql.append("select a.client_id clientId, ");
		sql.append("sum(a.owe_fee) totalFee, ");
		sql.append("max(a.client_name) clientName, ");
		sql.append("sum(q) oweFee, ");
		sql.append("max(a.charge_date) lastChargeDate, ");
		sql.append("case sum(q) when 0 then null else max(a.next_charge_date) end nextChargeDate ");
		sql.append("from (select t.client_id, ");
		sql.append("t.order_id, ");
		sql.append("max(t.owe_fee) owe_fee, ");
		sql.append("max(s.client_name) client_name, ");
		sql.append("max(t.charge_date) charge_date, ");
		sql.append("max(t.next_charge_date) next_charge_date, ");
		sql.append("max(t.owe_fee) - sum(t.real_pay_fee) q ");
		sql.append("from tb_charge_record t ");
		sql.append("left join tb_client s on t.client_id = s.client_id ");
		sql.append("where 1 = 1 ");

		// 判断客户、订单
		if (clientName != null && !"".equals(clientName)) {
			sql.append("and s.client_name like '%" + clientName + "%' ");
		}
		if (orderId != null && !"".equals(orderId)) {
			sql.append("and t.order_id = '" + orderId + "' ");
		}

		// 判断时间区间
		StringBuffer temp = new StringBuffer();
		if (startDate != null && !"".equals(startDate)) {
			temp.append(" and next_charge_date >= '" + startDate + "' ");
		}
		if (endDate != null && !"".equals(endDate)) {
			temp.append(" and next_charge_date <= '" + endDate + "' ");
		}
		if (temp.length() > 0) {
			sql.append("and t.client_id in (select client_id from tb_charge_record where 1 = 1 ");
			sql.append(temp.toString());
			sql.append(")");
		}

		sql.append("group BY t.client_id, t.order_id) a ");
		sql.append("group BY a.client_id HAVING SUM(q) > 0 ");
		
		if(pageCounts != null){
			sql.append("limit " + (offset == null ? 0 : offset.intValue()));
			sql.append(", " + pageCounts.intValue());
		}

		// 创建SQLquery对象
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置列类型，避免Column not found
		query.addScalar("clientId", Hibernate.STRING);
		query.addScalar("totalFee", Hibernate.DOUBLE);
		query.addScalar("clientName", Hibernate.STRING);
		query.addScalar("oweFee", Hibernate.DOUBLE);
		query.addScalar("lastChargeDate", Hibernate.DATE);
		query.addScalar("nextChargeDate", Hibernate.DATE);

		return query.list();
	}

	public List<?> queryOweDetail(String clientId, String orderId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select (SELECT i.client_name FROM tb_client i WHERE i.client_id = c.r_client) rClientName, ");
		sql.append("(SELECT i.client_name FROM tb_client i WHERE i.client_id = c.s_client) sClientName, ");
		sql.append("case b.client_id when c.r_client then 'r' else 's' end clientType, ");
		sql.append("c.s_client_name sLinkMan, c.r_client_name rLinkMan, ");
		sql.append("c.s_client_phone sPhone, c.r_client_phone rPhone, ");
		sql.append("c.s_address sAddress, c.r_address rAddress, ");
		sql.append("c.cargo_name cargoName, c.cargo_norm cargoNorm, ");
		sql.append("c.cargo_quantity cargoQuantity, c.unit, c.unit_price unitPrice, ");
		sql.append("c.s_time sTime, c.r_time rTime, c.charge_type chargeType, c.total_fee totalFee, ");
		sql.append("c.s_charge sCharge, c.r_charge rCharge, b.owefee oweFee, b.realpayfee realPayFee, ");
		sql.append("b.next_charge_date nextChargeDate, b.last_charge_date lastChargeDate, ");
		sql.append("b.next_charge nextCharge, b.last_charge lastCharge ");
		sql.append("from (select a.order_id, a.client_id, ");
		sql.append("max(a.owe_fee) owefee, sum(a.real_pay_fee) realpayfee, ");
		sql.append("max(a.charge_date) last_charge_date, ");
		sql.append("max(a.next_charge_date) next_charge_date, ");
		sql.append("(select a.real_pay_fee ");
		sql.append("from tb_charge_record i ");
		sql.append("where i.client_id = a.client_id ");
		sql.append("and i.order_id = a.order_id ");
		sql.append("order by i.entry_time limit 1) last_charge, ");
		sql.append("(select a.next_charge_fee ");
		sql.append("from tb_charge_record i ");
		sql.append("where i.client_id = a.client_id ");
		sql.append("and i.order_id = a.order_id ");
		sql.append("order by i.entry_time limit 1) next_charge ");
		sql.append("from tb_charge_record a ");
		sql.append("where a.client_id = ? ");
		sql.append("and a.order_id like ? ");
		sql.append("group by a.order_id ");
		sql.append("having max(a.owe_fee) - sum(a.real_pay_fee) > 0) b ");
		sql.append("inner join tb_invoice_order c on b.order_id = c.order_id ");
		// 创建SQLquery对象
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置列类型，避免Column not found
		query.addScalar("rClientName", Hibernate.STRING);
		query.addScalar("sClientName", Hibernate.STRING);
		query.addScalar("clientType", Hibernate.STRING);
		query.addScalar("sLinkMan", Hibernate.STRING);
		query.addScalar("rLinkMan", Hibernate.STRING);
		query.addScalar("sPhone", Hibernate.STRING);
		query.addScalar("rPhone", Hibernate.STRING);
		query.addScalar("sAddress", Hibernate.STRING);
		query.addScalar("rAddress", Hibernate.STRING);
		query.addScalar("cargoName", Hibernate.STRING);
		query.addScalar("cargoNorm", Hibernate.STRING);
		query.addScalar("cargoQuantity", Hibernate.DOUBLE);
		query.addScalar("unitPrice", Hibernate.DOUBLE);
		query.addScalar("unit", Hibernate.STRING);
		query.addScalar("sTime", Hibernate.DATE);
		query.addScalar("rTime", Hibernate.DATE);
		query.addScalar("chargeType", Hibernate.INTEGER);
		query.addScalar("totalFee", Hibernate.DOUBLE);
		query.addScalar("sCharge", Hibernate.DOUBLE);
		query.addScalar("rCharge", Hibernate.DOUBLE);
		query.addScalar("oweFee", Hibernate.DOUBLE);
		query.addScalar("realPayFee", Hibernate.DOUBLE);
		query.addScalar("lastChargeDate", Hibernate.DATE);
		query.addScalar("nextChargeDate", Hibernate.DATE);
		query.addScalar("lastCharge", Hibernate.DOUBLE);
		query.addScalar("nextCharge", Hibernate.DOUBLE);
		// 设置分页参数
		query.setParameter(0, clientId);
		query.setParameter(1, orderId == null ? "%" : "%" + orderId + "%");

		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryChargeHistory(String clientId,
			String orderId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select a.order_id orderId, ");
		//依赖数据库自定义函数
		sql.append("getTotalFee(a.client_id, a.ORDER_ID) totalfee, ");
		sql.append("a.owe_fee oweFee, a.real_pay_fee realPayFee, a.charge_date chargeDate, ");
		sql.append("a.owe_fee - a.real_pay_fee curretOwe, ");
		sql.append("a.note ");
		sql.append("from tb_charge_record a ");
		sql.append("where a.real_pay_fee > 0 and a.client_id = ? ");
		sql.append("and a.order_id like ? ");
		sql.append("order by a.entry_time desc");

		// 创建SQLquery对象
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置列类型，避免Column not found
		query.addScalar("orderId", Hibernate.STRING);
		query.addScalar("totalfee", Hibernate.DOUBLE);
		query.addScalar("oweFee", Hibernate.DOUBLE);
		query.addScalar("realPayFee", Hibernate.DOUBLE);
		query.addScalar("curretOwe", Hibernate.DOUBLE);
		query.addScalar("chargeDate", Hibernate.DATE);
		query.addScalar("note", Hibernate.TEXT);

		query.setParameter(0, clientId);
		query.setParameter(1, orderId == null ? "%" : "%" + orderId + "%");
		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryResumeForHistory(String clientId,
			String orderId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(DISTINCT c.order_id) countOrders, ");
		sql.append("COUNT(1) - COUNT(DISTINCT c.order_id) countCharges, ");
		sql.append("(SELECT SUM(OWE_FEE) FROM tb_charge_record i ");
		sql.append("WHERE i.ORDER_ID = c.order_id AND i.client_id = c.client_id AND i.real_pay_fee = 0) totalFee, ");
		sql.append("SUM(REAL_PAY_FEE) resumeCharge ");
		sql.append("FROM tb_charge_record c ");
		sql.append("where c.client_id = ? ");
		sql.append("and c.order_id like ? ");

		// 创建SQLquery对象
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置列类型，避免Column not found
		query.addScalar("countOrders", Hibernate.INTEGER);
		query.addScalar("countCharges", Hibernate.INTEGER);
		query.addScalar("totalFee", Hibernate.DOUBLE);
		query.addScalar("resumeCharge", Hibernate.DOUBLE);

		query.setParameter(0, clientId);
		query.setParameter(1, orderId == null ? "%" : "%" + orderId + "%");
		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> loadFeeBySendClientForPage(
			String clientName, String linkMan, String startDate, String endDate, Integer offset,
			Integer limit) {
		StringBuffer sql = new StringBuffer();
		sql.append("select b.client_name clientName, b.linkMan, a.entry_time entryTime, ");
		sql.append("a.cargo_name cargoName, a.unit_price unitPrice, a.unit, ");
		sql.append("a.cargo_quantity cargoQuantity, a.total_fee totalFee, a.s_charge charge ");
		sql.append("from tb_invoice_order a join tb_client b on a.r_client = b.client_id ");
		sql.append("where b.client_name like ? and b.linkman like ? ");
		if (startDate != null && !"".equals(startDate)) {
			sql.append(" and a.entry_time >= '" + startDate + "' ");
		}
		if (endDate != null && !"".equals(endDate)) {
			sql.append(" and a.entry_time <= '" + endDate + "' ");
		}
		if(limit != null){
			sql.append("limit " + (offset == null ? 0 : offset.intValue()));
			sql.append(", " + limit.intValue());
		}
		
		// 创建SQLquery对象
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		// 设置列类型，避免Column not found
		query.addScalar("clientName", Hibernate.STRING);
		query.addScalar("linkMan", Hibernate.STRING);
		query.addScalar("entryTime", Hibernate.DATE);
		query.addScalar("cargoName", Hibernate.STRING);
		query.addScalar("unitPrice", Hibernate.DOUBLE);
		query.addScalar("unit", Hibernate.STRING);
		query.addScalar("cargoQuantity", Hibernate.DOUBLE);
		query.addScalar("totalFee", Hibernate.DOUBLE);
		query.addScalar("charge", Hibernate.DOUBLE);

		query.setParameter(0, clientName == null ? "%" : "%" + clientName + "%");
		query.setParameter(1, linkMan == null ? "%" : "%" + linkMan + "%");
		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> loadFeeBySendClientForResume(
			String clientName, String linkMan, String startDate, String endDate) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) countOrder, ifnull(sum(a.total_fee),0) totalFee, ifnull(sum(s_charge),0) charge ");
		sql.append("from tb_invoice_order a join tb_client b on a.r_client = b.client_id ");
		sql.append("where b.client_name like ? and b.linkman like ? ");
		if (startDate != null && !"".equals(startDate)) {
			sql.append(" and a.entry_time >= '" + startDate + "' ");
		}
		if (endDate != null && !"".equals(endDate)) {
			sql.append(" and a.entry_time <= '" + endDate + "' ");
		}
		
		// 创建SQLquery对象
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		// 设置列类型，避免Column not found
		query.addScalar("countOrder", Hibernate.INTEGER);
		query.addScalar("totalFee", Hibernate.DOUBLE);
		query.addScalar("charge", Hibernate.DOUBLE);
		
		query.setParameter(0, clientName == null ? "%" : "%" + clientName + "%");
		query.setParameter(1, linkMan == null ? "%" : "%" + linkMan + "%");
		
		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> loadFeeByTruckForPage(String truckId,
			String truckName, String startDate, String endDate, Integer offset,
			Integer limit) {
		String[] temp = {"", "", "", "", "", ""};
		if(startDate != null && !"".equals(startDate)){
			temp[0] = new String("AND i1.PETROL_DATE >= '"+ startDate +"' ");
			temp[2] = new String("AND i1.FIX_DATE >= '"+ startDate +"' ");
			temp[4] = new String("AND b.S_TIME >= '"+ startDate +"' ");
		}
		if(endDate != null && !"".equals(endDate)){
			temp[1] = new String("AND i1.PETROL_DATE <= '"+ endDate +"' ");
			temp[3] = new String("AND i1.FIX_DATE <= '"+ endDate +"' ");
			temp[5] = new String("AND b.s_time <= '"+ endDate +"' ");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.TRUCK_ID truckId, a.order_id orderId, max(c.TRUCK_NAME) truckName, max(b.s_time) sTime, ");
		sql.append("sum(a.LOAD_PERTIME * a.trans_times) quantity, sum(a.trans_times) transTimes, ");
		sql.append("max(c.MAX_LOAD) maxLoad, sum(a.LOAD_PERTIME * a.trans_times * b.unit_price) totalFee, ");
		sql.append("(SELECT IFNULL(SUM(i1.PETROL_QUANTITY),0) FROM TB_COST_PETROL i1 ");
		sql.append("WHERE i1.order_id = a.order_id and i1.TRUCK_ID = a.TRUCK_ID ");
		sql.append(temp[0].toString() + temp[1].toString());
		sql.append(" ) petrolQuantity, ");
		sql.append("(SELECT IFNULL(SUM(i1.PETROL_QUANTITY * i1.unit_price),0) FROM TB_COST_PETROL i1 ");
		sql.append("WHERE i1.order_id = a.order_id and i1.TRUCK_ID = a.TRUCK_ID ");
		sql.append(temp[0].toString() + temp[1].toString());
		sql.append(" ) petrolCharge, ");
		sql.append("(SELECT IFNULL(SUM(i1.TOTAL_CHARGE),0) FROM TB_COST_FIX i1 ");
		sql.append("WHERE i1.order_id = a.order_id and i1.TRUCK_ID = a.TRUCK_ID ");
		sql.append(temp[2].toString() + temp[3].toString());
		sql.append(" ) fixCharge, ");
		sql.append("(SELECT count(1) FROM TB_COST_FIX i1 ");
		sql.append("WHERE i1.order_id = a.order_id and i1.TRUCK_ID = a.TRUCK_ID ");
		sql.append(temp[2].toString() + temp[3].toString());
		sql.append(" ) fixCount ");
		sql.append(" FROM tb_invoice_info a LEFT JOIN tb_invoice_order b ON a.order_id = b.order_id ");
		sql.append("LEFT JOIN tb_truck_info c ON a.TRUCK_ID = c.TRUCK_ID ");
		sql.append("WHERE a.TRUCK_ID LIKE ? and c.TRUCK_NAME LIKE ? ");
		sql.append(temp[4].toString() + temp[5].toString());
		sql.append(" group by a.TRUCK_ID, a.order_id order by b.entry_time desc ");
		if(limit != null){
			sql.append("limit " + (offset == null ? 0 : offset.intValue()));
			sql.append(", " + limit.intValue());
		}
		
		// 创建SQLquery对象
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		// 设置列类型，避免Column not found
		query.addScalar("truckId", Hibernate.STRING);
		query.addScalar("orderId", Hibernate.STRING);
		query.addScalar("truckName", Hibernate.STRING);
		query.addScalar("maxLoad" ,Hibernate.DOUBLE);
		query.addScalar("sTime", Hibernate.DATE);
		query.addScalar("quantity", Hibernate.DOUBLE);
		query.addScalar("totalFee", Hibernate.DOUBLE);
		query.addScalar("transTimes", Hibernate.INTEGER);
		query.addScalar("petrolQuantity", Hibernate.DOUBLE);
		query.addScalar("petrolCharge", Hibernate.DOUBLE);
		query.addScalar("fixCharge", Hibernate.DOUBLE);
		query.addScalar("fixCount", Hibernate.INTEGER);

		query.setParameter(0, truckId == null ? "%" : "%" + truckId + "%");
		query.setParameter(1, truckName == null ? "%" : "%" + truckName + "%");
		
		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> loadFeeByDriverForPage(String driverName,
			String startDate, String endDate, Integer offset, Integer limit) {
		String[] temp = {"", "", "", "", "", ""};
		if(startDate != null && !"".equals(startDate)){
			temp[0] = new String("AND i1.ILLEGAL_DATE >= '"+ startDate +"' ");
			temp[2] = new String("AND i1.ACCIDENT_TIME >= '"+ startDate +"' ");
			temp[4] = new String("AND d.S_TIME >= '"+ startDate +"' ");
		}
		if(endDate != null && !"".equals(endDate)){
			temp[1] = new String("AND i1.ILLEGAL_DATE <= '"+ endDate +"' ");
			temp[3] = new String("AND i1.ACCIDENT_TIME <= '"+ endDate +"' ");
			temp[5] = new String("AND d.s_time <= '"+ endDate +"' ");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  MAX(a.name) driverName, MAX(a.driver_license) driverLicense, MAX(a.license_year) licenseYear, ");
		sql.append("COUNT(DISTINCT c.ORDER_ID) sentTimes,SUM(c.TRANS_TIMES * c.LOAD_PERTIME * d.UNIT_PRICE) totalFee, ");
		sql.append("(SELECT COUNT(1) FROM TB_COST_ACCIDENT i1  WHERE i1.order_id = c.order_id ");
		sql.append("AND i1.truck_id = c.TRUCK_ID AND i1.driver_id = a.DRIVER_ID ");
		sql.append(temp[2] + temp[3]);
		sql.append(") accTimes, ");
		sql.append("(SELECT SUM(i1.LOSSES) FROM TB_COST_ACCIDENT i1  WHERE i1.order_id = c.order_id ");
		sql.append("AND i1.truck_id = c.TRUCK_ID AND i1.driver_id = a.DRIVER_ID ");
		sql.append(temp[2] + temp[3]);
		sql.append(") accCharge, ");
		sql.append("(SELECT COUNT(1) FROM TB_COST_FINE i1 WHERE i1.order_id = c.order_id ");
		sql.append("AND i1.truck_id = c.TRUCK_ID AND i1.driver_id = a.DRIVER_ID ");
		sql.append(temp[0] + temp[1]);
		sql.append(") fineTimes, ");
		sql.append("(SELECT SUM(i1.FINE_CHARGE) FROM TB_COST_FINE i1 WHERE i1.order_id = c.order_id ");
		sql.append("AND i1.truck_id = c.TRUCK_ID AND i1.driver_id = a.DRIVER_ID ");
		sql.append(temp[0] + temp[1]);
		sql.append(") fineCharge ");
		sql.append("FROM tb_driver_info a LEFT JOIN tb_driver_workload b ON a.DRIVER_ID = b.DRIVER_ID ");
		sql.append("LEFT JOIN tb_invoice_info c ON b.INVOICE_ID = c.ID LEFT JOIN tb_invoice_order d ON c.ORDER_ID = d.ORDER_ID ");
		sql.append("WHERE a.name LIKE ? ");
		sql.append(temp[4] + temp[5]);
		sql.append("GROUP BY a.DRIVER_ID, c.order_id order by d.entry_time desc ");
		if(limit != null){
			sql.append("limit " + (offset == null ? 0 : offset.intValue()));
			sql.append(", " + limit.intValue());
		}
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		// 设置列类型，避免Column not found
		query.addScalar("driverName", Hibernate.STRING);
		query.addScalar("driverLicense", Hibernate.STRING);
		query.addScalar("licenseYear", Hibernate.STRING);
		query.addScalar("sentTimes" ,Hibernate.INTEGER);
		query.addScalar("totalFee", Hibernate.DOUBLE);
		query.addScalar("accTimes", Hibernate.INTEGER);
		query.addScalar("accCharge", Hibernate.DOUBLE);
		query.addScalar("fineTimes", Hibernate.INTEGER);
		query.addScalar("fineCharge", Hibernate.DOUBLE);

		query.setParameter(0, driverName == null ? "%" : "%" + driverName + "%");
		return query.list();
	}
}
